package ipt.pru.Fila1;

import ipt.enumeracion.Rol;
import ipt.exp.AlumnoException;
import ipt.vo.Alumno;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 

public class AlumnoJDBCDAO implements AlumnoDAO {

	Connection connection = null;
	PreparedStatement ptmt = null;
	ResultSet resultSet = null;

	private final static String TABLE_NAME = "ALUMNOS";
	// Columnas
	private final static String COL_ID = "id";
	private final static String COL_DNI = "dni";
	private final static String COL_NOMBRE = "nombre";
	private final static String COL_APELLIDO1 = "apellido1";
	private final static String COL_APELLIDO2 = "apellido2";
	private final static String COL_EDAD = "edad";
	private final static String COL_ROL = "rol";
	private final static String COL_EMAIL = "email";

	public AlumnoJDBCDAO() {
		super();
	}

	private Connection getConnection() throws SQLException {
		Connection conn;
		conn = ConnectionFactory.getInstance().getConnection();
		return conn;
	}

	public List<Alumno> getAlumnos() {

		List<Alumno> listaAlu = new ArrayList();

		try {

			String queryString = "SELECT * FROM " + TABLE_NAME;
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			resultSet = ptmt.executeQuery();
			while (resultSet.next()) {
				Alumno alu = new Alumno();
				alu.setDni(resultSet.getString(COL_DNI));
				alu.setNombre(resultSet.getString(COL_NOMBRE));
				alu.setApellido1(resultSet.getString(COL_APELLIDO1));
				alu.setApellido2(resultSet.getString(COL_APELLIDO2));
				alu.setEdad(resultSet.getInt(COL_EDAD));
				switch (resultSet.getInt(COL_ROL)) {
				case 0:
					alu.setRol(Rol.TRABAJADOR);
					break;
				case 1:
					alu.setRol(Rol.JEFE);
					break;
				case 2:
					alu.setRol(Rol.CLIENTE);
					break;
				default:
					System.out.println("El rol no existe");
					break;
				}
				alu.setEmail(resultSet.getString(COL_EMAIL));

				listaAlu.add(alu);
				alu = null;

			}

		} catch (AlumnoException e) {
			e.printStackTrace();

		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
			ex = ex.getNextException();
			System.out.println("");

		} finally {

			try {
				if (resultSet != null)
					resultSet.close();
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return listaAlu;
	}

	@Override
	public Alumno getAlumnoByDni(String dni) throws AlumnoException {
		Alumno alu = new Alumno();
		try {

			String queryString = "SELECT * FROM " + TABLE_NAME
					+ " WHERE DNI = '" + dni + "'";

			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			resultSet = ptmt.executeQuery();
			while (resultSet.next()) {

				alu.setDni(resultSet.getString(COL_DNI));
				alu.setNombre(resultSet.getString(COL_NOMBRE));
				alu.setApellido1(resultSet.getString(COL_APELLIDO1));
				alu.setApellido2(resultSet.getString(COL_APELLIDO2));
				alu.setEdad(resultSet.getInt(COL_EDAD));
				switch (resultSet.getInt(COL_ROL)) {
				case 0:
					alu.setRol(Rol.TRABAJADOR);
					break;
				case 1:
					alu.setRol(Rol.JEFE);
					break;
				case 2:
					alu.setRol(Rol.CLIENTE);
					break;
				default:
					System.out.println("El rol no existe");
					break;
				}
				alu.setEmail(resultSet.getString(COL_EMAIL));
			}

		} catch (AlumnoException e) {
			e.printStackTrace();

		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
			ex = ex.getNextException();
			System.out.println("");

		} finally {

			try {
				if (resultSet != null)
					resultSet.close();
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return alu;

	}

	@Override
	public boolean udapteAlumno(Alumno alu) throws AlumnoException {
		int res = 0;
		boolean success = false;
		try {
		
			String queryString = "UPDATE " + TABLE_NAME + " SET NOMBRE = '"
					+ alu.getNombre() + " ', APELLIDO1 = '"
							+ alu.getApellido1() + " ', APELLIDO2 = '" 
							+ alu.getApellido2() + "' , EDAD = " 
							+ alu.getEdad() + " , ROL = '" 
							+ 0 + "' , EMAIL = '" 
							+ alu.getEmail() + "' WHERE ID = 2";
						
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			res = ptmt.executeUpdate();
			System.out.println("Table Updated Successfully");

		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
			ex = ex.getNextException();
			System.out.println("");

		} finally {
			try {
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();

			}
		}
		if (res == 0) {
			success = false;

		} else if (res == 1) {// ("Table Updated Successfully")
			success = true;

		}

		return success;
	}

	@Override
	public boolean deleteAlumno(String dni) throws AlumnoException {
		int res = 0;
		boolean success = false;
		try {
			String queryString = "DELETE FROM " + TABLE_NAME + " WHERE DNI = '"
					+ dni + "'";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			res = ptmt.executeUpdate();
			System.out.println("Data deleted Successfully");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();

			} catch (SQLException ex) {
				System.out.println("Message: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("ErrorCode: " + ex.getErrorCode());
				ex = ex.getNextException();
				System.out.println("");

			} catch (Exception e) {
				e.printStackTrace();
			}

			if (res == 0) {
				success = false;

			} else if (res == 1) {// ("Data deleted Successfully");
				success = true;

			}
		}
		return success;
	}

	@Override
	public boolean insertAlumno(Alumno alu) throws AlumnoException {
		int res = 0;
		boolean success = false;
		try {

			String queryString = "INSERT INTO "
					+ TABLE_NAME
					+ " (DNI, NOMBRE, APELLIDO1, APELLIDO2, EDAD, ROL, EMAIL)VALUES ("
					+ "'" + alu.getDni() + "', '" + alu.getNombre()
					+ "', '" + alu.getApellido1() + "', '" + alu.getApellido2()
					+ "', " + alu.getEdad() + ", " + 0 + ", " + "'"
					+ alu.getEmail() + "')";

			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			res = ptmt.executeUpdate();

			System.out.println("Data Added Successfully");

		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
			ex = ex.getNextException();
			System.out.println("");

		} finally {
			try {
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		if (res == 0) {
			success = false;

		} else if (res == 1) {// ("Data Added Successfully");
			success = true;

		}

		return success;
	}

}
